SpdrDropExecution

V8 Message Definiton

METADATA

Attribute	Value
Topic	1450-away-drop
MLink Token	SystemData
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
dropNumber	BIGINT	PRI	0	SR drop record number
sysEnvironment	enum - SysEnvironment		'None'	original source sys environment Stable Current etc
runStatus	enum - RunStatus		'None'	original source run status ProdBeta
routingCode	TINYTEXT		''
routingSeqNum	INT		0
secKey_at	enum - AssetType		'None'
secKey_ts	enum - TickerSrc		'None'
secKey_tk	VARCHAR(12)		''
secKey_yr	SMALLINT UNSIGNED		0
secKey_mn	TINYINT UNSIGNED		0
secKey_dy	TINYINT UNSIGNED		0
secKey_xx	DOUBLE		0
secKey_cp	enum - CallPut		'Call'
secType	enum - SpdrKeyType		'None'
securityDesc	VARCHAR(16)		''
securityType	VARCHAR(4)		''
symbol	VARCHAR(12)		''	symbol symbolSfx
maturityDate	DATETIME(6)		'1900-01-01 00:00:00.000000'
strike	DOUBLE		0
callPut	enum - CallPut		'Call'
account	TINYTEXT		''	FixTag1
giveupFirm	VARCHAR(6)		''
giveupAccnt	TINYTEXT		''
clearingFirm	VARCHAR(6)		''
clearingAccnt	TINYTEXT		''
dropData1	TINYTEXT		''
dropData2	TINYTEXT		''
clOrdId	VARCHAR(32)		''	child order clOrdId if available
fillVersion	TINYINT UNSIGNED		0	cancelcorrect version number initial fill is version1
fillExecStatus	enum - ExecStatus		'None'	SpiderRock execution status FillBustCorrectRejectSysRej
fillDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	fill DateTime from SRDateTime
fillQuantity	INT		0	fill size from execReportlastSize
fillSide	enum - BuySell		'None'	fill side from execReportside
fillSSaleFlag	enum - ShortSaleFlag		'None'	fill side from execReportside
fillPositionType	enum - PositionType		'None'
fillPrice	DOUBLE		0	fill price from execReportlastPrice
fillTransactDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	fill DateTime from execReportfillDttm
fillMarket	VARCHAR(8)		''	fill market from execReportlastMarket
fillSRLiqTag	VARCHAR(2)		''	fill SR normalized liquidity tag
fillExchLiqTag	VARCHAR(4)		''	fill exchange liquidity code
fillExchFee	FLOAT		0	fill exchange fee if available
fillExecId	TINYTEXT		''	fill execution id from execReportexecId
fillRefExecId	TINYTEXT		''	fill ref execution id from execReportrefExecId only set on a cancel or correct
fillLegRefId	BIGINT		0	fill leg ref id from execReportlegRefId
fillFirmType	VARCHAR(16)		''	fill firm type as reported from street side
fillExecBroker	VARCHAR(16)		''	fill execution broker as reported from street side
fillOrderCapacity	enum - OrderCapacity		'None'	fill order capacity as reported from street side
fillContraDetail	VARCHAR(16)		''	fill contra party additional detail if available
fillContraCapacity	enum - OrderCapacity		'None'	fill order capacity of contra party if available
fillStreetText	TINYTEXT		''
netTimestamp	BIGINT		0	drop gateway arrival timestamp
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
dropNumber	1

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgSRDropExecution` (
    `dropNumber` BIGINT NOT NULL DEFAULT 0 COMMENT 'SR drop record number',
    `sysEnvironment` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None' COMMENT 'original (source) sys environment [Stable, Current, etc]',
    `runStatus` ENUM('None','Prod','Beta','UAT','SysTest') NOT NULL DEFAULT 'None' COMMENT 'original (source) run status [Prod,Beta]',
    `routingCode` TINYTEXT NOT NULL DEFAULT '',
    `routingSeqNum` INT NOT NULL DEFAULT 0,
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0,
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None',
    `securityDesc` VARCHAR(16) NOT NULL DEFAULT '',
    `securityType` VARCHAR(4) NOT NULL DEFAULT '',
    `symbol` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'symbol (+ ''.'' + symbolSfx)',
    `maturityDate` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `strike` DOUBLE NOT NULL DEFAULT 0,
    `callPut` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `account` TINYTEXT NOT NULL DEFAULT '' COMMENT 'FixTag.1',
    `giveupFirm` VARCHAR(6) NOT NULL DEFAULT '',
    `giveupAccnt` TINYTEXT NOT NULL DEFAULT '',
    `clearingFirm` VARCHAR(6) NOT NULL DEFAULT '',
    `clearingAccnt` TINYTEXT NOT NULL DEFAULT '',
    `dropData1` TINYTEXT NOT NULL DEFAULT '',
    `dropData2` TINYTEXT NOT NULL DEFAULT '',
    `clOrdId` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'child order clOrdId (if available)',
    `fillVersion` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cancel/correct version number (initial fill is version=1)',
    `fillExecStatus` ENUM('None','Fill','Bust','Correct','Reject','SysRej') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock execution status (Fill,Bust,Correct,Reject,SysRej)',
    `fillDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'fill DateTime (from SRDateTime)',
    `fillQuantity` INT NOT NULL DEFAULT 0 COMMENT 'fill size (from execReport.lastSize)',
    `fillSide` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None' COMMENT 'fill side (from execReport.side)',
    `fillSSaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None' COMMENT 'fill side (from execReport.side)',
    `fillPositionType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None',
    `fillPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'fill price (from execReport.lastPrice)',
    `fillTransactDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'fill DateTime (from execReport.fillDttm)',
    `fillMarket` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'fill market (from execReport.lastMarket)',
    `fillSRLiqTag` VARCHAR(2) NOT NULL DEFAULT '' COMMENT 'fill SR normalized liquidity tag',
    `fillExchLiqTag` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'fill exchange liquidity code',
    `fillExchFee` FLOAT NOT NULL DEFAULT 0 COMMENT 'fill exchange fee (if available)',
    `fillExecId` TINYTEXT NOT NULL DEFAULT '' COMMENT 'fill execution id (from execReport.execId)',
    `fillRefExecId` TINYTEXT NOT NULL DEFAULT '' COMMENT 'fill ref execution id (from execReport.refExecId) [only set on a cancel or correct]',
    `fillLegRefId` BIGINT NOT NULL DEFAULT 0 COMMENT 'fill leg ref id (from execReport.legRefId)',
    `fillFirmType` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'fill firm type (as reported from street side)',
    `fillExecBroker` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'fill execution broker (as reported from street side)',
    `fillOrderCapacity` ENUM('None','Agency','Principal','Individual','Proprietary','AgentOtherMember','RisklessPrincipal') NOT NULL DEFAULT 'None' COMMENT 'fill order capacity (as reported from street side)',
    `fillContraDetail` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'fill contra party additional detail (if available)',
    `fillContraCapacity` ENUM('None','Agency','Principal','Individual','Proprietary','AgentOtherMember','RisklessPrincipal') NOT NULL DEFAULT 'None' COMMENT 'fill order capacity of contra party (if available)',
    `fillStreetText` TINYTEXT NOT NULL DEFAULT '',
    `netTimestamp` BIGINT NOT NULL DEFAULT 0 COMMENT 'drop gateway arrival timestamp',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`dropNumber`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `dropNumber`,
    `sysEnvironment`,
    `runStatus`,
    `routingCode`,
    `routingSeqNum`,
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `securityDesc`,
    `securityType`,
    `symbol`,
    `maturityDate`,
    `strike`,
    `callPut`,
    `account`,
    `giveupFirm`,
    `giveupAccnt`,
    `clearingFirm`,
    `clearingAccnt`,
    `dropData1`,
    `dropData2`,
    `clOrdId`,
    `fillVersion`,
    `fillExecStatus`,
    `fillDttm`,
    `fillQuantity`,
    `fillSide`,
    `fillSSaleFlag`,
    `fillPositionType`,
    `fillPrice`,
    `fillTransactDttm`,
    `fillMarket`,
    `fillSRLiqTag`,
    `fillExchLiqTag`,
    `fillExchFee`,
    `fillExecId`,
    `fillRefExecId`,
    `fillLegRefId`,
    `fillFirmType`,
    `fillExecBroker`,
    `fillOrderCapacity`,
    `fillContraDetail`,
    `fillContraCapacity`,
    `fillStreetText`,
    `netTimestamp`,
    `timestamp`
FROM `SRTrade`.`MsgSRDropExecution`
WHERE 
    /* Replace with a BIGINT */ 
    `dropNumber` = 1234567890;

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='SpdrDropExecution' ORDER BY ordinal_position ASC;